package com.plug.controller;

import com.plug.common.consts.Code;
import com.plug.common.jdbc.DBConnectionManager;
import com.plug.common.jdbc.JDBCInfoBean;
import com.plug.common.model.ExceptionResult;
import com.plug.common.model.JsonResult;
import com.plug.controller.util.AbstractController;
import com.plug.controller.util.TaskThread;
import com.plug.entity.PlugContrast;
import com.plug.entity.PlugDatasource;
import com.plug.service.IPlugContrastService;
import com.plug.service.IPlugDatasourceService;
import com.plug.service.IPlugRecordService;
import com.plug.service.IPlugTaskStateService;
import com.plug.util.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by slaye on 2016/12/22.
 */
@Controller
@RequestMapping("/task")
public class TaskController extends AbstractController {

    private DBConnectionManager dbConnectionManager = DBConnectionManager.getInstance();

    static final Logger logger = LoggerFactory.getLogger(TaskController.class);

    @Autowired
    private IPlugContrastService plugContrastService;

    @Autowired
    private IPlugDatasourceService plugDatasourceService;

    @Autowired
    private IPlugRecordService plugRecordService;

    @Autowired
    private IPlugTaskStateService plugTaskStateService;


    /**
     *
     * @param plugContrast 任务配置对象
     * @param column 对比列名称
     * @param sort 排序方式
     * @param limit 分页大小
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param columnTime 时间列
     * @param isTime 判断条件方式
     * @param primaryKey 主键开始位
     * @param request
     * @return
     */
    @RequestMapping(value = "/immediately", method = RequestMethod.POST)
    @ResponseBody
    public ResponseEntity<Object> immediately(PlugContrast plugContrast,
                                              @RequestParam("column")String column,
                                              @RequestParam("sort")String sort,
                                              @RequestParam("limit")Integer limit,
                                              @RequestParam("startTime")String startTime,
                                              @RequestParam("endTime")String endTime,
                                              @RequestParam("columnTime")String columnTime,
                                              @RequestParam("isTime")Boolean isTime,
                                              @RequestParam("primaryKey")Long primaryKey,
                                              HttpServletRequest request){
        try{
            plugContrast = plugContrastService.findDatasourceByName(plugContrast.getSrcDsId(),plugContrast.getTgtDsId(),plugContrast.getSrcTab());
            if(column.indexOf("全部") != -1){
                column = "*";
            } else if(column.indexOf(plugContrast.getPrimaryKey()) == -1) {
                String[] columns = column.split(",");
                StringBuffer sb = new StringBuffer("`" + plugContrast.getPrimaryKey()+ "`,");
                for (int i = 0; i < columns.length; i++) {
                    sb.append("`" + columns[i] + "`,");
                }
                sb.deleteCharAt(sb.length() - 1);
                column =  sb.toString();
            }
            PlugDatasource srcDataSource = plugDatasourceService.findByID(plugContrast.getSrcDsId());//获取源数据源对象
            PlugDatasource tgtDataSource = plugDatasourceService.findByID(plugContrast.getTgtDsId());//获取目标数据源对象
            Long total = 0L;
            Long[] indexLong = new Long[2];
            if(isTime) {
                if(!StringUtils.isNull(startTime) && !StringUtils.isNull(endTime) && !StringUtils.isNull(columnTime)) {
                    Map<String, Object> tableStrutMap = plugRecordService.findTableStrut(tgtDataSource, plugContrast.getTgtTab());//查询表时间数据结构
                    total = plugRecordService.findHistoryRecordTotal(tgtDataSource,plugContrast.getTgtTab(),0L,columnTime,
                            tableStrutMap.get(columnTime).toString(),startTime,endTime,plugContrast,true);
                    indexLong = plugRecordService.getMaxAndMinIndex(tgtDataSource,plugContrast.getTgtTab(),column,sort,columnTime,
                            tableStrutMap.get(columnTime).toString(),startTime,endTime,plugContrast);
                }
            } else {
                total = plugRecordService.findHistoryRecordTotal(tgtDataSource,plugContrast.getTgtTab(),primaryKey,null,
                        null,null,null,plugContrast,false);
                indexLong = plugRecordService.getMaxAndMinIndex(tgtDataSource,plugContrast.getTgtTab(),null,null,column,sort,plugContrast);
                indexLong[0] = primaryKey;
            }

            JsonResult jsonResult = new JsonResult();
            jsonResult.setCode(Code.SUCCESS);
            if(total != 0){
                jsonResult.setMessage("及时对比任务正常执行，请根据任务日志查看最新对比状态！");
                TaskThread taskThread = new TaskThread(srcDataSource,tgtDataSource,plugContrast,column,
                        indexLong,total,plugRecordService,plugTaskStateService,request);
                Thread thread = new Thread(taskThread);
                thread.start();
            } else {
                jsonResult.setMessage("及时对比任务对比总数为0条！");
            }


            return new ResponseEntity<Object>(jsonResult, HttpStatus.OK);
        } catch (Exception e){
            return new ResponseEntity<Object>(new ExceptionResult(e), HttpStatus.OK);
        }
    }

    @RequestMapping(value = "/task", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> task(){
        Map<String, Object> resultMap = new HashMap<>();
        String message = "";
        List<PlugContrast> list = plugContrastService.findAll();
        try{
            for (PlugContrast p : list) {
                PlugDatasource srcPlugDatasource = plugDatasourceService.findByID(p.getSrcDsId());//获取源数据源
                PlugDatasource tgtPlugDatasource = plugDatasourceService.findByID(p.getTgtDsId());//获取目标数据源
                logger.debug("srcPlugDatasource.getDsName():{}",srcPlugDatasource.getDsName());
                this.createPools(srcPlugDatasource);
                this.createPools(tgtPlugDatasource);
                Connection srcConn = dbConnectionManager.getConnection(srcPlugDatasource.getDsName());
                logger.debug("srcConn.srcConn():{}",srcConn);
                if(srcConn == null) {
                    srcConn = dbConnectionManager.getConnection(srcPlugDatasource.getDsName());
                }
                logger.debug("tgtPlugDatasource.getDsName():{}",tgtPlugDatasource.getDsName());
                Connection tgtConn = dbConnectionManager.getConnection(tgtPlugDatasource.getDsName());
                logger.debug("tgtConn.tgtConn():{}",tgtConn);
                if(tgtConn == null) {
                    tgtConn = dbConnectionManager.getConnection(tgtPlugDatasource.getDsName());
                }
                PreparedStatement ps = null;
                ResultSet rs = null;
                ps = tgtConn.prepareStatement("select id from " + p.getTgtTab() + " order by id desc");
                logger.debug("查询最大ID数:{}", "select id from " + p.getTgtTab() + " order by id desc");
                rs = ps.executeQuery();
                Long maxIndex = 0L;
                message += "源数据表名：" + p.getSrcTab()+",目标数据表名：" + p.getTgtTab()+",";
                while (rs.next()) {
                    maxIndex = rs.getLong("id");
                    break;
                }
                message += "起始数据ID为：" + maxIndex+",";
                rs.close();
                ps.close();
                ps = srcConn.prepareStatement("select * from " + p.getSrcTab() + " where id > " + maxIndex);
                logger.debug("根据最大ID数查询未备份数据:{}", "select * from " + p.getSrcTab() + " where id > " + maxIndex);
                rs = ps.executeQuery();
                ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
                int colCount = metaData.getColumnCount(); // 取得所有列的个数
                StringBuffer sqlColumn = new StringBuffer("insert into " + p.getTgtTab() + " (");
                StringBuffer sqlValue = new StringBuffer();
                for (int j = 1; j <= colCount; j++) {
                    sqlColumn.append(metaData.getColumnName(j) + ",");
                    sqlValue.append("?,");
                }
                sqlColumn.deleteCharAt(sqlColumn.length() - 1);
                sqlValue.deleteCharAt(sqlValue.length() - 1);
                sqlColumn.append(") values (" + sqlValue.toString() + ")");
                tgtConn.setAutoCommit(false);
                PreparedStatement tgtPs = tgtConn.prepareStatement(sqlColumn.toString());
                logger.debug("新增备份数据SQL:{}", sqlColumn.toString());
                message += "一共备份"+rs.getRow()+"行数据！";
                while (rs.next()){
                    for (int j = 1; j <= colCount; j++) {
                        Object obj = rs.getObject(j);
                        tgtPs.setObject(j, obj);
                    }
                    tgtPs.addBatch();
                }
                tgtPs.executeBatch();
                tgtConn.commit();
                tgtPs.close();
                logger.debug("备份{}数据完成", p.getTgtTab());
            }
        } catch (Exception e){
            message += e.getMessage();
            e.printStackTrace();
        }
        resultMap.put("msg", message);
        return resultMap;
    }

    /**
     * 创建数据库连接池
     * @param plugDatasource
     */
    private void createPools(PlugDatasource plugDatasource) {
        JDBCInfoBean jdbcInfoBean = new JDBCInfoBean();
        jdbcInfoBean.setName(plugDatasource.getDsName());
        jdbcInfoBean.setDriver(plugDatasource.getDsDriver());
        jdbcInfoBean.setPassword(plugDatasource.getDsPassword());
        jdbcInfoBean.setUrl(plugDatasource.getDsUrl());
        jdbcInfoBean.setUsername(plugDatasource.getDsUsername());
        dbConnectionManager.createPools(jdbcInfoBean);
    }
}
